#include "SqliteDialog.h"
#include "ui_SqliteDialog.h"

SqliteDialog::SqliteDialog(QWidget *parent)
    : QDialog(parent)
    , ui(new Ui::SqliteDialog)
{
    ui->setupUi(this);
    //创建数据库
    createDB();
    //创建数据表
    createTable();
}

SqliteDialog::~SqliteDialog()
{
    delete ui;
}

//创建数据库
void SqliteDialog::createDB (void)
{
    //创建数据库
    db = QSqlDatabase::addDatabase("QSQLITE");
    //设置数据库的名称
    db.setDatabaseName ("menu.db");
    //打开数据库
    if (false == db.open ()) {
        qDebug() << db.lastError().text();
    }
}

//创建学生成绩数据表
void SqliteDialog::createTable (void)
{
    QSqlQuery query;
    QString str = QString ("CREATE TABLE student"
             "(id INT PRIMARY KEY NOT NULL,"
             "name TEXT NOT NULL,"
             "score REAL NOT NULL)");
    qDebug() << str;

    query.exec (str);
}
//查询
void SqliteDialog::queryTable (void)
{
    QString str = QString ("SELECT * FROM student");
    //执行str, 保存结果到model中
    model.setQuery(str);
    //显示model
    ui->tableView->setModel(&model);
}

//点击增加按钮对应的槽函数
void SqliteDialog::on_insertButton_clicked()
{
    QSqlQuery query;
    int id = ui->idEdit->text().toInt();
    QString name = ui->nameEdit->text();
    double score = ui->scoreEdit->text().toDouble();

    QString str = QString("INSERT INTO student VALUES(%1, '%2', %3)"
                          ).arg(id).arg(name).arg(score);

    query.exec(str);
    //再次查询并显示
    queryTable();
}

//点击删除按钮对应的槽函数
void SqliteDialog::on_deleteButton_clicked()
{
    QSqlQuery query;

    int id = ui->idEdit->text().toInt();
    QString str = QString (
                "DELETE FROM student WHERE id=%1").arg(id);

    query.exec(str);
    //再次查询并显示
    queryTable();
}

//点击修改按钮执行的槽函数,根据ID修改成绩
void SqliteDialog::on_updateButton_clicked()
{
    QSqlQuery query;
    int id = ui->idEdit->text().toInt();
    double score = ui->scoreEdit->text().toDouble();
    QString str = QString (
                "UPDATE student SET score=%1 WHERE id=%2").arg(score).arg(id);
    query.exec(str);
    //再次查询并显示
    queryTable();
}

//排序按钮对应的槽函数
void SqliteDialog::on_sortButton_clicked()
{
    //获取排序的列名
    QString value = ui->valueComboBox->currentText();
    //获取排序的方式
    QString condition;
    if (ui->condComboBox->currentIndex() == 0) {
        condition = "ASC";
    } else {
        condition = "DESC";
    }
    QString str = QString (
                "SELECT * FROM student ORDER BY %1 %2").arg(value).arg(condition);

    model.setQuery(str);
    ui->tableView->setModel(&model);
}

